﻿
using Castle.Core.Internal;
using Coldairarrow.Business.Interface;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.Interface;
using Coldairarrow.Util;
using Dynamitey;
using Newtonsoft.Json.Linq;
using OfficeOpenXml.Drawing.Slicer.Style;
using org.apache.zookeeper.data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics.Tracing;
using System.Drawing;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Security.Cryptography;
using System.Threading;
using System.Threading.Tasks;
using static AutoMapper.Internal.CollectionMapperExpressionFactory;
using static Coldairarrow.Entity.Interface.YDOrdersDTO.Orders.Delivery;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace Coldairarrow.Business.Interface
{
    public class OL_Rep_AMZReturnsInterfaceBusiness : Interface_BaseBusiness, IJob_Business, ITransientDependency
    {
        public List<OL_FilterDTO> Filters;
        public OL_Rep_AMZReturnsInterfaceBusiness()
        {
            this.InterfaceName = "电商退货";
            //获取字段的映射关系
            tableField = GetMapField("editRow");
            //索引字段
            Keys = GetKeys("editRow");
            indexFields.Add(Keys.Where(X => X.Type == "K").First().Field);
            //查询过滤条件
            Filters = new List<OL_FilterDTO>();


            //查询执行前的事件
            BeforeQueryEvent += InterfaceBusiness_BeforeQueryEvent;
            //查询执行中的事件
            OnQueryEvent += InterfaceBusiness_OnQueryEvent;
            //执行非查询请求中的事件
            OnNoQueryEvent += InterfaceBusiness_OnNoQueryEvent;

        }

        /// <summary>
        /// 执行非查询请求中时间
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="noQueryEventArgs"></param>
        private void InterfaceBusiness_OnNoQueryEvent(object sender, NoQueryEventArgs noQueryEventArgs)
        {
            //API请求是否成功
            noQueryEventArgs.Success = noQueryEventArgs.Result["success"].ToString().ToBool();
        }

        /// <summary>
        /// 查询执行前的事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="queryEventArgs"></param>
        private void InterfaceBusiness_BeforeQueryEvent(object sender, QueryEventArgs queryEventArgs)
        {
            //执行查询前添加过滤条件
            if (queryEventArgs.Page.Filters.IsNullOrEmpty() && !Filters.IsNullOrEmpty())
            {
                queryEventArgs.Page.Filters = Filters;
            }

        }

        /// <summary>
        /// 查询执行中的事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="queryEventArgs"></param>
        private void InterfaceBusiness_OnQueryEvent(object sender, QueryEventArgs queryEventArgs)
        {
            //是否查询完成
            if (queryEventArgs.Result["data"]["rows"].ToList().Count == 0)
            {
                queryEventArgs.Page.Complete = true;
            }
            //向总行中添加新的行
            queryEventArgs.Rows.AddRange(queryEventArgs.Result["data"]["rows"].ToList());
            //获取总页数
            queryEventArgs.Page.Total = queryEventArgs.Result["data"]["total"].ToString().ToInt();
            //获取查询成功标志
            queryEventArgs.Success = queryEventArgs.Result["success"].ToString().ToBool();
        }


        /// <summary>
        /// 执行增删改查业务
        /// </summary>
        public async void ExecuteInterface()
        {

            try
            {
                //数据库连接
                ConString = JsonSettingHelper.GetSetting("Database:BaseDb:ConnectionString");
                SqlDb = (SqlServerHelper)DbHelperFactory.GetDbHelper(EFCore.Sharding.DatabaseType.SqlServer, ConString);

                //获取此接口的所有API信息
                this.interfaceOption = SqlDb.GetListBySql<Base_InterfaceOption>(@$"SELECT * FROM  Base_InterfaceOption(NOLOCK) WHERE Name='{InterfaceName}' ");
                Base_InterfaceOption queryOption = this.interfaceOption.Where(o => o.OperateType == "getFilterRows").First();//查询API配置
                Base_InterfaceOption insertOption = this.interfaceOption.Where(o => o.OperateType == "addRow").First();//新增API配置
                Base_InterfaceOption upDateOption = this.interfaceOption.Where(o => o.OperateType == "editRow").First();//更行API配置
                Base_InterfaceOption deleteOption = this.interfaceOption.Where(o => o.OperateType == "deleteRow").First();//删除API配置
                
                if (DateTime.Now.Hour >= 1 && DateTime.Now.Hour <= 5)
                {
                    queryOption.ExcInterval = 160;
                }
                //获取退货表的开始日期
                string dateForm = DateTime.Now.AddDays(-queryOption.ExcInterval).ToString("yyyy-MM-dd");
                List<string> dateList = new List<string>();
                //获取所有需要计算的数据的日期
                while (queryOption.ExcInterval != -1)
                {
                    dateList.Add(DateTime.Now.AddDays(-queryOption.ExcInterval).ToString("yyyy-MM-dd"));
                    queryOption.ExcInterval--;
                }
                string dateAMZ = dateForm.ToDateTime().AddYears(-1).ToString("yyyy-MM-dd");

                //获取一年前亚马逊报表最晚日期
                List<PrdPerformance> prdPerformanceList = SqlDb.GetListBySql<PrdPerformance>(@$"SELECT * FROM  SD_LX_PrdPerformance(NOLOCK) WHERE Date >='{dateAMZ}' AND (volume >0 OR return_goods_count >0)");

                //获取赔偿报告
                List<Reimbursement> reimbursementList = SqlDb.GetListBySql<Reimbursement>(@$"SELECT * FROM SD_LX_Reimbursement WHERE approval_date_locale >='{dateAMZ}' AND reason ='CustomerReturn' ");

                //获取FBA退货
                List<FBAReturnOrder> returnOrderList = SqlDb.GetListBySql<FBAReturnOrder>(@$"SELECT * FROM SD_LX_FBAReturnOrder WHERE return_date_locale >='{dateAMZ}'");

                //获取FBM退货
                List<FBMReturnOrder> returnOrderList_FBM = SqlDb.GetListBySql<FBMReturnOrder>(@$"SELECT * FROM SD_LX_FBMReturnOrder WHERE return_date >='{dateAMZ}' AND  
     -- 首先确保两个字段都能成功转换为数字，避免除以零
    TRY_CAST(refunded_amount AS DECIMAL(18,2)) IS NOT NULL
    AND TRY_CAST(order_amount AS DECIMAL(18,2)) IS NOT NULL
    -- 然后确保订单金额不为零，防止除法运算错误
    AND TRY_CAST(order_amount AS DECIMAL(18,2)) <> 0
    -- 最后计算比例并筛选
    AND (TRY_CAST(refunded_amount AS DECIMAL(18,2)) / TRY_CAST(order_amount AS DECIMAL(18,2))) * 100 > 30");

                //获取亚马逊订单
                List<AMZOrderDetail> amzOrderDetail = SqlDb.GetListBySql<AMZOrderDetail>(@$"SELECT * FROM SD_LX_AMZOrderDetail WHERE purchase_date_local >='{dateForm}'");

                //获取One_Link亚马逊MSKU信息
                AMZ_MSKUInterfaceBusiness aMZ_MSKU = new AMZ_MSKUInterfaceBusiness();
                aMZ_MSKU.Filters.Clear();
                aMZ_MSKU.Filters.Add(
               //13:>,14>=15:<,16<=
               new OL_FilterDTO() { ControlId = "685aa0b5a31d84c7f659fa16", DataType = 30, SpliceType = 1, FilterType = 2, Value = "10001" }//只查询亚马逊的MSKU
           );
                List<AMZ_MSKU> AMZ_MSKUList = await aMZ_MSKU.QueryAsync<AMZ_MSKU>(aMZ_MSKU.queryOption, aMZ_MSKU.tableField, aMZ_MSKU.SqlDb);
                AMZ_MSKUList.ForEach(x =>
                {
                    //SKU
                    List<AssTable> table = x.sku_lock.ToObject<List<AssTable>>();
                    if (!table.IsNullOrEmpty() && table.Count > 0) x.sku_lock_T = table[0];
                    x.sku_lock = x.sku_lock_T.IsNullOrEmpty() ? "" : x.sku_lock_T.sid;
                });

                //获取One_Link SKU信息
                OL_LocalSKUInterfaceBusiness localSKU = new OL_LocalSKUInterfaceBusiness();
                localSKU.Filters.Clear();
                localSKU.Filters.Add(
               //13:>,14>=15:<,16<=
               new OL_FilterDTO() { ControlId = "", DataType = 15, SpliceType = 1, FilterType = 14, Value = "" }//FDATE字段过滤日开始700天前到现在的数据
           );
                List<LocalSKU> localSKUList = await localSKU.QueryAsync<LocalSKU>(localSKU.queryOption, localSKU.tableField, localSKU.SqlDb);

                //获取店铺
                OL_ShopInterfaceBusiness OL_Shop = new OL_ShopInterfaceBusiness();
                OL_Shop.Filters.Add(
            new OL_FilterDTO() { ControlId = "", DataType = 29, SpliceType = 1, FilterType = 8, Value = "" }
                //new OL_FilterDTO() { ControlId = "661d1c34387bc49b7a55d06e", DataType = 15, SpliceType = 1, FilterType = 14, Value = "2024-11-24" }
                );
                List<OL_Shop> shopList = OL_Shop.Query<OL_Shop>(OL_Shop.queryOption, OL_Shop.tableField, OL_Shop.SqlDb);
                //设置订单列表的店铺ID
                amzOrderDetail.ForEach(x =>
                {
                    var seller = shopList.Where(y => y.Name == x.seller_name);
                    if (!seller.IsNullOrEmpty())
                    {
                        x.sid = seller.First().ID;


                    }
                });

                List<Rep_AMZReturns> sourceData = new List<Rep_AMZReturns>();
                if (prdPerformanceList.Count > 0)
                {
                    //获取指定时间内需要更新的销售数据和赔偿数据
                    var prdGroup = prdPerformanceList.Where(x => x.date.ToDateTime() >= dateForm.ToDateTime()).ToList();
                    var reimbGroup = reimbursementList.Where(x => x.approval_date_locale.ToDateTime().Date >= dateForm.ToDateTime()).ToList();
                    //当日赔偿对应的退货项
                    List<FBAReturnOrder> reiReturnThatDay = new List<FBAReturnOrder>();
                    List<FBMReturnOrder> reiReturnThatDay_FBM = new List<FBMReturnOrder>();
                    //当日的赔偿项
                    List<Reimbursement> reiThatDay = new List<Reimbursement>();
                    //一年赔偿对应的退货项
                    List<FBAReturnOrder> reiReturnOneYear = new List<FBAReturnOrder>();
                    List<FBMReturnOrder> reiReturnOneYear_FBM = new List<FBMReturnOrder>();
                    //一年的赔偿项
                    List<Reimbursement> reiOneYear = new List<Reimbursement>();



                    List<string> oKMsku = new List<string>();

                    //用本地SKU每一天的数据
                    localSKUList.ForEach(sku =>
                    {
                        //获取第一条数做查询
                        //var firstItem = item;
                        //var msku = firstItem.seller_sku;
                        //var sid = firstItem.sid;
                        //var date = firstItem.date.ToDateTime().Date;

                        //获取SKU对应的所有MSKU
                        var MSKUList = AMZ_MSKUList.Where(x => x.sku_lock == sku.RowId).Select(x =>
                        {
                            var seller_sku = x.seller_sku.IsNullOrEmpty() ? " " : x.seller_sku;
                            var sid = x.sid.IsNullOrEmpty() ? " " : x.sid;
                            return seller_sku + "_Split_" + sid;
                        }).Distinct();

                        //根据SKU对应的多个MSKU开始生成每一行数据
                        MSKUList.ForEach(msku_sid =>
                        {
                            dateList.ForEach(date_s =>
                            {
                                //ID标志
                                var date = date_s.ToDateTime();
                                var msku_sid_ = msku_sid.Split("_Split_");
                                var sid = msku_sid_[1];
                                var msku = msku_sid_[0];
                                var flag = msku + sid + date.ToString("yyyy-MM-dd");
                                //判断是否已执行过
                                if (!oKMsku.Contains(flag))
                                {
                                    //添加到已生成数据列表中
                                    oKMsku.Add(flag);
                                    Rep_AMZReturns AMZReturn = new Rep_AMZReturns();

                                    #region 日退货
                                    //当日的销售/退货
                                    var sale = prdGroup.Where(x => x.seller_sku == msku && x.sid == sid && x.date.ToDateTime().Date == date);
                                    var saleThatDay = !sale.IsNullOrEmpty() && sale.Count() > 0 ? sale.Sum(x => x.volume.ToDouble()) : 0;
                                    var returnThatDay = !sale.IsNullOrEmpty() && sale.Count() > 0 ? sale.Sum(x => x.return_goods_count.ToDouble()) : 0;
                                    //产品表现不包含FBM退货数量，需要从FBM退货单中获取
                                    reiReturnThatDay_FBM.Clear();
                                    reiReturnThatDay_FBM = returnOrderList_FBM.Where(x => x.return_date.ToDateTime().Date == date && x.seller_sku == msku).ToList();
                                    if (!reiReturnThatDay_FBM.IsNullOrEmpty())
                                    {
                                        returnThatDay = returnThatDay + (double)reiReturnThatDay_FBM.Sum(x => x.return_quantity);
                                    }

                                    //当日的赔偿项
                                    reiThatDay = reimbGroup.Where(x => x.msku == msku && x.sid == sid && x.approval_date_locale.ToDateTime().Date == date).ToList();
                                    var reiOrderList = reiThatDay.Select(y => y.amazon_order_id).Distinct();
                                    //获取当日赔偿对应的退货
                                    reiReturnThatDay.Clear();//FBA
                                    if (!reiOrderList.IsNullOrEmpty()) reiReturnThatDay = returnOrderList.Where(x => reiOrderList.Contains(x.order_id) && x.sku == msku).ToList();

                                    //计算当日赔偿数量
                                    decimal indemnifyThatDay = 0;
                                    if (!reiReturnThatDay.IsNullOrEmpty())//FBA
                                    {
                                        indemnifyThatDay = (decimal)reiReturnThatDay.Sum(x => x.quantity.ToDouble());
                                    }
                                    #endregion


                                    #region 年退货
                                    //一年内的销售/退货项
                                    var saleOneYear = prdPerformanceList.Where(x => x.seller_sku == msku && x.sid == sid && x.date.ToDateTime().Date >= date.AddYears(-1) && x.date.ToDateTime().Date <= date);
                                    var saleThatYear = saleOneYear.Sum(x => x.volume.ToDouble());
                                    var returnThatYear = saleOneYear.Sum(x => x.return_goods_count.ToDouble());
                                    //产品表现不包含FBM退货数量，需要从FBM退货单中获取
                                    reiReturnThatDay_FBM.Clear();//FBM直接获取当年退货项
                                    reiReturnOneYear_FBM = returnOrderList_FBM.Where(x => x.seller_sku == msku && x.return_date.ToDateTime().Date > date.AddYears(-1) && x.return_date.ToDateTime().Date <= date).ToList();
                                    if (!reiReturnOneYear_FBM.IsNullOrEmpty()) returnThatYear = returnThatYear + (double)reiReturnOneYear_FBM.Sum(x => x.return_quantity);//FBM


                                    //一年内的赔偿项
                                    reiOneYear = reimbursementList.Where(x => x.msku == msku && x.sid == sid && x.approval_date_locale.ToDateTime().Date > date.AddYears(-1) && x.approval_date_locale.ToDateTime().Date <= date).ToList();
                                    var reiOrderOneYearList = reiOneYear.Select(y => y.amazon_order_id).Distinct();
                                    //获取一年内赔偿对应的退货
                                    reiReturnOneYear.Clear();//FBA
                                    if (!reiOrderOneYearList.IsNullOrEmpty()) reiReturnOneYear = returnOrderList.Where(x => reiOrderOneYearList.Contains(x.order_id) && x.sku == msku).ToList();

                                    //计算一年内赔偿数量
                                    decimal indemnifyThatYear = 0;
                                    if (!reiReturnOneYear.IsNullOrEmpty()) indemnifyThatYear = (decimal)reiReturnOneYear.Sum(x => x.quantity.ToDouble());//FBA

                                    //移除已纳入计算的赔偿项
                                    reimbGroup.RemoveAll(x => x.msku == msku && x.sid == sid && x.approval_date_locale.ToDateTime().Date == date);
                                    #endregion 年退货


                                    #region 订单退货
                                    //根据MSKU，时间获取对应的订单
                                    var orderList = amzOrderDetail.Where(x => x.seller_sku == msku && x.sid == sid && x.purchase_date_local.ToDateTime().ToString("yyyy-MM-dd") == date.ToString("yyyy-MM-dd"));
                                    int saleOrder = 0;
                                    int indemnifyOrder = 0;
                                    int returnOrder = 0;

                                    if (!orderList.IsNullOrEmpty())
                                    {
                                        //var orderNumbers = orderList.Select(x => x.amazon_order_id).Distinct();
                                        //下单数量
                                        saleOrder = orderList.Sum(x => x.quantity_ordered.ToInt());

                                        foreach (var item1 in orderList)
                                        {
                                            //退货数量FBA
                                            var fbaReturn = returnOrderList.Where(x => x.order_id == item1.amazon_order_id && x.sku == item1.seller_sku && item1.RowId != "OK");
                                            if (!fbaReturn.IsNullOrEmpty() && fbaReturn.Count() > 0)
                                            {
                                                returnOrder = returnOrder + fbaReturn.Sum(x => x.quantity.ToInt());
                                                //将已经赋值的做好标记防止重复
                                                fbaReturn.ForEach(x => x.RowId = "OK");


                                                //需要退货数量大于0的才去取赔偿数量
                                                var reimbursement = reimbursementList.Where(x => x.amazon_order_id == item1.amazon_order_id && x.sid == sid && x.msku == item1.seller_sku && item1.RowId != "OK");
                                                if (!reimbursement.IsNullOrEmpty() && reimbursement.Count() > 0)
                                                {
                                                    indemnifyOrder = indemnifyOrder + reimbursement.Sum(x => (int)x.quantity_reimbursed_total);
                                                    //将已经赋值的做好标记防止重复
                                                    reimbursement.ForEach(x => x.RowId = "OK");
                                                }

                                            }

                                            //退货数量FBM
                                            var fbmReturn = returnOrderList_FBM.Where(x => x.order_id == item1.amazon_order_id && x.seller_sku == item1.seller_sku && item1.RowId != "OK");
                                            if (!fbmReturn.IsNullOrEmpty() && fbmReturn.Count() > 0)
                                            {
                                                returnOrder = returnOrder + fbmReturn.Sum(x => (int)x.return_quantity);
                                                //将已经赋值的做好标记防止重复
                                                fbmReturn.ForEach(x => x.RowId = "OK");
                                            }

                                        }



                                    }
                                    #endregion 订单退货



                                    //所有值为0不新增
                                    if (!(saleThatDay == 0 && returnThatDay == 0 && indemnifyThatDay == 0 && saleThatYear == 0 && returnThatYear == 0 && indemnifyThatYear == 0))
                                    {
                                        var msku_OL = AMZ_MSKUList.Where(x => x.seller_sku == msku && x.sid == sid);
                                        AMZReturn.ID = flag;
                                        AMZReturn.MSKU = !msku_OL.IsNullOrEmpty() && msku_OL.Count() > 0 ? msku_OL.First().RowId : "";
                                        AMZReturn.MSKU_S = msku;
                                        AMZReturn.Date = date.ToString();
                                        AMZReturn.IndemnifyOnThatDay = indemnifyThatDay.ToString();
                                        AMZReturn.SalesOnThatDay = saleThatDay.ToString();
                                        AMZReturn.ReturnsOnThatDay = returnThatDay.ToString();
                                        AMZReturn.IndemnifyOnThatYear = indemnifyThatYear.ToString();
                                        AMZReturn.SalesOnThatYear = saleThatYear.ToString();
                                        AMZReturn.ReturnsOnThatYear = returnThatYear.ToString();
                                        AMZReturn.SalesOrder = saleOrder.ToString();
                                        AMZReturn.ReturnsOrder = returnOrder.ToString();
                                        AMZReturn.IndemnifyOrder = indemnifyOrder.ToString();
                                        AMZReturn.Seller = sid;
                                        sourceData.Add(AMZReturn);
                                    }

                                }
                            });
                        });

                    });
                    //如果赔偿项大于0
                    //if (reimbGroup.Count > 0)
                    //{
                    //    var reimbGroup2 = reimbGroup.GroupBy(x => x.msku + x.approval_date_locale + x.sid);
                    //    reimbGroup2.ForEach(item2 =>
                    //    {
                    //        //获取第一条数做查询
                    //        var firstItem2 = item2.First();
                    //        var msku = firstItem2.msku;
                    //        var sid = firstItem2.sid;
                    //        var date = firstItem2.approval_date_locale.ToDateTime().Date;
                    //        Rep_AMZReturns AMZReturn2 = new Rep_AMZReturns();
                    //        if (msku == "HEU-FDES-ET40G-F11" && date.ToString("yyyy-MM-dd") == "2025-03-26")
                    //        {
                    //            var test = "";

                    //        }
                    //        //当日的赔偿项
                    //        var reiThatDay2 = reimbGroup.Where(x => x.msku == msku && x.sid == sid && x.approval_date_locale.ToDateTime().Date == date);
                    //        var reiOrderList2 = reiThatDay2.Select(y => y.amazon_order_id).Distinct();
                    //        //获取当日赔偿对应的退货
                    //        reiReturnThatDay.Clear();//FBA
                    //        if (!reiOrderList2.IsNullOrEmpty()) reiReturnThatDay = returnOrderList.Where(x => reiOrderList2.Contains(x.order_id) && x.sku == msku).ToList();
                    //        //reiReturnThatDay_FBM.Clear();//FBM直接获取当日退货项
                    //        //reiReturnThatDay_FBM = returnOrderList_FBM.Where(x => x.return_date.ToDateTime().Date == date && x.seller_sku == msku).ToList();
                    //        //计算当日赔偿数量
                    //        decimal indemnifyThatDay2 = 0;
                    //        //FBA
                    //        if (!reiReturnThatDay.IsNullOrEmpty()) indemnifyThatDay2 = (decimal)reiReturnThatDay.Sum(x => x.quantity.ToDouble());
                    //        //FBM直接获取当日退货项
                    //        //if (!reiReturnThatDay_FBM.IsNullOrEmpty()) indemnifyThatDay2 = indemnifyThatDay2 + reiReturnThatDay_FBM.Sum(x => x.return_quantity);

                    //        //一年内的销售/退货项
                    //        var saleOneYear2 = prdPerformanceList.Where(x => x.seller_sku == msku && x.sid == sid && x.date.ToDateTime().Date >= date.AddYears(-1) && x.date.ToDateTime().Date <= date);
                    //        var saleThatYear2 = saleOneYear2.Sum(x => x.volume.ToDouble());
                    //        var returnThatYear2 = saleOneYear2.Sum(x => x.return_goods_count.ToDouble());
                    //        //FBM直接获取当年退货项
                    //        reiReturnOneYear_FBM = returnOrderList_FBM.Where(x => x.return_date.ToDateTime().Date >= date.AddYears(-1) && x.return_date.ToDateTime().Date <= date && x.seller_sku == msku).ToList();
                    //        if (!reiReturnOneYear_FBM.IsNullOrEmpty()) returnThatYear2 = returnThatYear2 + (double)reiReturnOneYear_FBM.Sum(x => x.return_quantity);
                    //        //一年内的赔偿项
                    //        var reiOneYear2 = reimbursementList.Where(x => x.msku == msku && x.sid == sid && x.approval_date_locale.ToDateTime().Date > date.AddYears(-1) && x.approval_date_locale.ToDateTime().Date <= date);
                    //        reiOrderList2 = reiOneYear2.Select(y => y.amazon_order_id).Distinct();
                    //        //获取一年内赔偿对应的退货
                    //        reiReturnOneYear.Clear();
                    //        //FBA
                    //        if (!reiOrderList2.IsNullOrEmpty()) reiReturnOneYear = returnOrderList.Where(x => reiOrderList2.Contains(x.order_id) && x.sku == msku).ToList();
                    //        //计算一年内赔偿数量
                    //        decimal indemnifyThatYear2 = 0;
                    //        if (!reiReturnOneYear.IsNullOrEmpty()) indemnifyThatYear2 = (decimal)reiReturnOneYear.Sum(x => x.quantity.ToDouble());

                    //        //获取ONE_LINK上的MSKU行ID
                    //        var msku_OL2 = AMZ_MSKUList.Where(x => x.seller_sku == msku && x.sid == sid);
                    //        AMZReturn2.ID = msku + sid + date.ToString("yyyy-MM--dd");
                    //        AMZReturn2.MSKU = !msku_OL2.IsNullOrEmpty() && msku_OL2.Count() > 0 ? msku_OL2.First().RowId : "";
                    //        AMZReturn2.MSKU_S = msku;
                    //        AMZReturn2.Seller = sid;
                    //        AMZReturn2.Date = date.ToString();
                    //        AMZReturn2.IndemnifyOnThatDay = indemnifyThatDay2.ToString();
                    //        AMZReturn2.IndemnifyOnThatYear = indemnifyThatYear2.ToString();
                    //        AMZReturn2.SalesOnThatYear = saleThatYear2.ToString();
                    //        AMZReturn2.ReturnsOnThatYear = returnThatYear2.ToString();
                    //        sourceData.Add(AMZReturn2);
                    //    });



                    //}

                }

                sourceData.GroupBy(x => x.Date.ToDateTime().ToString("yyyy-MM") + x.MSKU_S + x.Seller).ForEach(x =>
                {
                    x.OrderBy(x => x.Date.ToDateTime()).Last().LastFlag = "Last";
                });

                //获取目标数据
                Filters.Clear();
                Filters.Add(
                //13:>,14>=15:<,16<=
                new OL_FilterDTO() { ControlId = "68084951a31d84c7f63ffe27", DataType = 15, SpliceType = 1, FilterType = 14, Value = dateForm }//FDATE字段过滤日开
            );
                List<Rep_AMZReturns> targetData = await QueryAsync<Rep_AMZReturns>(queryOption, tableField, SqlDb);
                foreach (var item1 in targetData)
                {
                    //MSKU
                    List<AssTable> table = item1.MSKU.ToObject<List<AssTable>>();
                    if (!table.IsNullOrEmpty() && table.Count > 0) item1.MSKU = table[0].sid;
                }
                if (this.State > 0 && sourceData.Count > 0)
                {
                    //更新
                    #region
                    List<Rep_AMZReturns> updataItem = sourceData.Intersect(targetData, new ComparerFromKeys<Rep_AMZReturns>(Keys, "RowId")).ToList();//获取交集(更新项)
                    List<OL_RowDTO> updataData = ConvertOL_Row(updataItem, tableField);//需要更新的数据集合
                    ExecuteNoQuery<OL_RowDTO>(updataData, upDateOption, SqlDb);//执行更新
                    #endregion

                    //新增
                    #region
                    List<Rep_AMZReturns> insertItem = sourceData.Except(targetData, new ComparerFromKey<Rep_AMZReturns>(indexFields.First(), "RowId")).ToList();//获取源数据对目标数据的差集(新增项)
                    List<OL_RowDTO> insertData = ConvertOL_Row(insertItem, tableField);//需要新增的数据集合
                    ExecuteNoQuery<OL_RowDTO>(insertData, insertOption, SqlDb);//执行插入行
                    #endregion

                    //删除
                    #region
                    List<Rep_AMZReturns> deleteItem = targetData.Except(sourceData, new ComparerFromKey<Rep_AMZReturns>(indexFields.First(), "RowId")).ToList();//获取目标数对据源数据的差集(删除项)
                    List<OL_RowDTO> deleteData = ConvertOL_Row(deleteItem, tableField);//需要删除的数据集合
                    ExecuteNoQuery<OL_RowDTO>(deleteData, deleteOption, SqlDb);//执行删除行
                    #endregion
                }
            }


            catch (Exception ex)
            {
                AddLog(SqlDb, ex.Message, InterfaceName, "erro", "");
            }
        }




        /// <summary>
        /// 将源数据根据字典映射关系转换成OL_RowDTO
        /// </summary>
        /// <typeparam name="T">源数据</typeparam>
        /// <param name="sourceData">源数据</param>
        /// <param name="map">Key为目标字段名称，Value为源字段名称</param>
        /// <returns></returns>
        public List<OL_RowDTO> ConvertOL_Row<T>(List<T> sourceData, Dictionary<string, string> map)
        {
            List<OL_RowDTO> list = new List<OL_RowDTO>();
            object rowId;
            foreach (var item in sourceData)
            {
                rowId = item.GetPropertyValue("RowId");
                //需要新增的数据
                OL_RowDTO row = new OL_RowDTO() { RowId = rowId.IsNullOrEmpty() ? null : rowId.ToString(), Controls = new List<Control>() };
                foreach (var c in map)
                {
                    //获取字段的键值做判断
                    var field = Keys.Where(x => x.TField == c.Key).ToList();
                    string isKey = "";
                    if (field.Count > 0) isKey = field.First().Type;
                    //判断对象的字段是否存在
                    if (item.ContainsProperty(c.Value) && isKey != "N")
                    {
                        //存在则添加数据
                        row.Controls.Add(new Control() { ControlId = c.Key, Value = item.GetPropertyValue(c.Value) });
                    }
                }
                list.Add(row);
            }
            return list;
        }


        /// <summary>
        /// 任务
        /// </summary>
        public void Job()
        {
            ExecuteInterface();
        }
    }

}
